﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Collections.ObjectModel;

namespace BambolettaDB
{
    class DBCommands
    {
        #region Static properties
        public static string Password = "";

        #endregion

        #region Public Methods
        public static SqlCeConnection ConnectDB()
        {
            return new SqlCeConnection(Properties.Settings.Default.BambolettaDBConnectionString + ";Password = " + Password);
        }

        public static bool TestConnection()
        {
            try
            {
                SqlCeConnection conn = ConnectDB();
                SqlCeCommand cmd = new SqlCeCommand("SELECT 1", conn);
                conn.Open();
                cmd.ExecuteReader();
                conn.Close();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
        #endregion

        #region RequestVM accessors
        public static ObservableCollection<RequestVM> GetRequests()
        {
            ObservableCollection<RequestVM> requests = new ObservableCollection<RequestVM>();

            try
            {
                SqlCeConnection conn = ConnectDB();
                SqlCeCommand cmd = new SqlCeCommand(@"SELECT Request.ID, Hair.Color, Hair.ID, Eyes.Color, Eyes.ID, Skin.Color, Skin.ID, Gender.Name, Gender.ID, Type.Description, Type.ID, Status.Description, Status.ID, Comment, Email, Customer.Name, DateRequested, Customer.ID, Customer.LocationID, Location.Description, Request.HairType, HairType.Description
                    FROM Request, Customer, Hair, Eyes, Skin, Gender, Type, Status, Location, HairType
                    WHERE Request.CustomerID = Customer.ID
                    AND Request.Eye = Eyes.ID
                    AND Request.Skin = Skin.ID
                    AND Request.Gender = Gender.ID
                    AND Request.Type = Type.ID
                    AND Request.HairType = HairType.ID
                    AND Customer.Status = Status.ID
                    AND Customer.LocationID = Location.ID
                    AND Request.Hair = Hair.ID", conn);
                conn.Open();

                SqlCeDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    RequestVM request = new RequestVM();
                    if (!rdr.IsDBNull(0)) request.ID = rdr.GetInt64(0);
                    if (!rdr.IsDBNull(1)) request.Hair = new StringItemVM(rdr.GetInt64(2),rdr.GetString(1),"Hair","Color");
                    if (!rdr.IsDBNull(3)) request.Eye = new StringItemVM(rdr.GetInt64(4), rdr.GetString(3), "Eyes", "Color");
                    if (!rdr.IsDBNull(5)) request.Skin = new StringItemVM(rdr.GetInt64(6), rdr.GetString(5), "Skin", "Color");
                    if (!rdr.IsDBNull(7)) request.Gender = new StringItemVM(rdr.GetInt64(8), rdr.GetString(7), "Gender", "Name");
                    if (!rdr.IsDBNull(9)) request.Type = new StringItemVM(rdr.GetInt64(10), rdr.GetString(9), "Hair", "Color");
                    if (!rdr.IsDBNull(11)) request.Customer.Status = new StringItemVM(rdr.GetInt64(12), rdr.GetString(11), "Status", "Description");
                    if (!rdr.IsDBNull(13)) request.Comment = rdr.GetString(13);
                    if (!rdr.IsDBNull(14)) request.Customer.Email = rdr.GetString(14);
                    if (!rdr.IsDBNull(15)) request.Customer.Name = rdr.GetString(15);
                    if (!rdr.IsDBNull(16)) request.Date = rdr.GetDateTime(16);
                    if (!rdr.IsDBNull(17)) request.Customer.ID = rdr.GetInt64(17);
                    if (!rdr.IsDBNull(19)) request.Customer.Location = new StringItemVM(rdr.GetInt64(18), rdr.GetString(19), "Location", "Description");
                    if (!rdr.IsDBNull(21)) request.HairType = new StringItemVM(rdr.GetInt64(20), rdr.GetString(21), "HairType", "Description");
                                        
                    requests.Add(request);
                }

                rdr.Close();

                conn.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine(ex.Message);
            }

            return requests;
        }

        public static void UpdateRequest(RequestVM request)
        {
            if (request.Customer.ID == -1)
                InsertCustomer(request.Customer);
            else
                UpdateCustomer(request.Customer);

            SqlCeConnection conn = ConnectDB();
            SqlCeCommand cmd = new SqlCeCommand("UPDATE Request SET Hair=" + request.Hair.ID + ", HairType = " + request.HairType.ID + ",Eye = " + request.Eye.ID + ", Skin = " + request.Skin.ID + ", Gender = " + request.Gender.ID + ", Type = " + request.Type.ID + ", Comment = '" + request.Comment + "', CustomerID = " + request.Customer.ID + " WHERE ID = " + request.ID, conn);
            conn.Open();
            int nRows = cmd.ExecuteNonQuery();
            conn.Close();
        }

        public static void InsertRequest(RequestVM request)
        {
            if (request.Customer.ID == -1)
                InsertCustomer(request.Customer);
            else
                UpdateCustomer(request.Customer);

            SqlCeConnection conn = ConnectDB();
            SqlCeCommand cmd = new SqlCeCommand("INSERT INTO Request (Hair, HairType, Eye, Skin, Gender, Type, Comment, CustomerID) VALUES (" + request.Hair.ID + ", " + request.HairType.ID + ", " + request.Eye.ID + ", " + request.Skin.ID + ", " + request.Gender.ID + ", " + request.Type.ID + ", '" + request.Comment + "', " + request.Customer.ID + ")", conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            cmd.CommandText = "SELECT CAST(@@IDENTITY AS BIGINT)";
            request.ID = (long)cmd.ExecuteScalar();
            conn.Close();
        }

        public static void DeleteRequest(RequestVM item)
        {
            SqlCeConnection conn = ConnectDB();
            SqlCeCommand cmd = new SqlCeCommand("DELETE FROM Request WHERE ID = " + item.ID, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public static void DeleteRequestForCustomer(CustomerVM item)
        {
            SqlCeConnection conn = ConnectDB();
            SqlCeCommand cmd = new SqlCeCommand("DELETE FROM Request WHERE CustomerID = " + item.ID, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        #endregion

        #region CustomerVM accessors
        public static ObservableCollection<CustomerVM> GetCustomers()
        {
            ObservableCollection<CustomerVM> customers = new ObservableCollection<CustomerVM>();

            try
            {
                SqlCeConnection conn = ConnectDB();
                SqlCeCommand cmd = new SqlCeCommand(@"SELECT Customer.ID, Name, Email, Status.ID, Status.Description, Customer.LocationID, Location.Description
                    FROM Customer, Status, Location
                    WHERE Customer.Status = Status.ID
                    AND Location.ID = Customer.LocationID", conn);
                conn.Open();

                SqlCeDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    CustomerVM cust = new CustomerVM();
                    if (!rdr.IsDBNull(0)) cust.ID = rdr.GetInt64(0);
                    if (!rdr.IsDBNull(1)) cust.Name = rdr.GetString(1);
                    if (!rdr.IsDBNull(3)) cust.Email = rdr.GetString(2);
                    if (!rdr.IsDBNull(4)) cust.Status = new StringItemVM(rdr.GetInt64(3), rdr.GetString(4), "Status", "Description");
                    if (!rdr.IsDBNull(6)) cust.Location = new StringItemVM(rdr.GetInt64(5), rdr.GetString(6), "Location", "Description");

                    customers.Add(cust);
                }

                rdr.Close();

                conn.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine(ex.Message);
            }

            return customers;
        }

        public static void DeleteCustomer(CustomerVM item)
        {
            SqlCeConnection conn = ConnectDB();
            SqlCeCommand cmd = new SqlCeCommand("DELETE FROM Customer WHERE ID = " + item.ID, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public static void InsertCustomer(CustomerVM customer)
        {
            SqlCeConnection conn = ConnectDB();
            SqlCeCommand cmd = new SqlCeCommand("INSERT INTO Customer (Email, Name, Status, LocationID) VALUES ('" + customer.Email + "', '" + customer.Name + "', " + customer.Status.ID + ", " + customer.Location.ID + ")", conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            cmd.CommandText = "SELECT CAST(@@IDENTITY AS BIGINT)";
            customer.ID = (long)cmd.ExecuteScalar();
            conn.Close();
        }

        public static void UpdateCustomer(CustomerVM customer)
        {
            SqlCeConnection conn = ConnectDB();
            SqlCeCommand cmd = new SqlCeCommand("UPDATE Customer SET Name = '" + customer.Name + "', Email = '" + customer.Email + "', Status = " + customer.Status.ID + ", LocationID = " + customer.Location.ID + " WHERE ID = " + customer.ID, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public static CustomerVM FindCustomer(string email)
        {

            try
            {
                SqlCeConnection conn = ConnectDB();
                SqlCeCommand cmd = new SqlCeCommand("SELECT Customer.ID, Customer.Name, Status.ID, Status.Description, Customer.LocationID, Location.Description FROM Customer, Status, Location WHERE Email = '" + email + "' AND Customer.Status = Status.ID AND Customer.LocationID = Location.ID", conn);
                conn.Open();

                SqlCeDataReader rdr = cmd.ExecuteReader();

                if (rdr.Read())
                {
                    CustomerVM vm = new CustomerVM();

                    if (!rdr.IsDBNull(0)) vm.ID = rdr.GetInt64(0);
                    if (!rdr.IsDBNull(1)) vm.Name = rdr.GetString(1);
                    if (!rdr.IsDBNull(3)) vm.Status = new StringItemVM(rdr.GetInt64(2), rdr.GetString(3), "Status", "Description");
                    if (!rdr.IsDBNull(5)) vm.Location = new StringItemVM(rdr.GetInt64(4), rdr.GetString(5), "Location", "Description");
                    vm.Email = email;

                    return vm;
                }

                rdr.Close();

                conn.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine(ex.Message);
            }

            return null;
        }
        #endregion

        #region StringItemVM accessors
        public static ObservableCollection<StringItemVM> GetEyes()
        {
            return GetStringItems("Eyes", "Color");
        }

        public static ObservableCollection<StringItemVM> GetHair()
        {
            return GetStringItems("Hair", "Color");
        }

        public static ObservableCollection<StringItemVM> GetHairTypes()
        {
            return GetStringItems("HairType", "Description");
        }

        public static ObservableCollection<StringItemVM> GetGender()
        {
            return GetStringItems("Gender", "Name");
        }

        public static ObservableCollection<StringItemVM> GetSkin()
        {
            return GetStringItems("Skin", "Color");
        }

        public static ObservableCollection<StringItemVM> GetTypes()
        {
            return GetStringItems("Type", "Description");
        }

        public static ObservableCollection<StringItemVM> GetStatus()
        {
            return GetStringItems("Status", "Description");
        }

        public static ObservableCollection<StringItemVM> GetLocations()
        {
            return GetStringItems("Location", "Description");
        }

        public static void InsertStringItem(StringItemVM item)
        {
            SqlCeConnection conn = ConnectDB();
            SqlCeCommand cmd = new SqlCeCommand("INSERT INTO " + item.SourceTable + " ( " + item.SourceColumn + " ) VALUES ( '" + item.Text + "' )", conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            cmd.CommandText = "SELECT CAST(@@IDENTITY AS BIGINT)";
            item.ID = (long)cmd.ExecuteScalar();
            conn.Close();
        }

        public static void UpdateStringItem(StringItemVM item)
        {
            SqlCeConnection conn = ConnectDB();
            SqlCeCommand cmd = new SqlCeCommand("UPDATE " + item.SourceTable + " SET " + item.SourceColumn + "='" + item.Text + "' WHERE ID = " + item.ID, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public static void DeleteStringItem(StringItemVM item)
        {
            SqlCeConnection conn = ConnectDB();
            SqlCeCommand cmd = new SqlCeCommand("DELETE FROM " + item.SourceTable + " WHERE ID = " + item.ID, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        private static ObservableCollection<StringItemVM> GetStringItems(string table, string column)
        {
            ObservableCollection<StringItemVM> items = new ObservableCollection<StringItemVM>();

            try
            {
                SqlCeConnection conn = ConnectDB();
                SqlCeCommand cmd = new SqlCeCommand("SELECT ID, " + column + " FROM " + table, conn);
                conn.Open();

                SqlCeDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    items.Add(new StringItemVM(rdr.GetInt64(0), rdr.GetString(1), table, column));
                }

                rdr.Close();

                conn.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine(ex.Message);
            }

            return items;
        }
        #endregion
    }
}
